package server;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import ocsf.server.ConnectionToClient;

import entities.User;

/**
 * establish a connection to the database
 * @author Lidor
 *
 */
public class mysqlConnection {

	public Connection conn;
/**
 * construct the connection with a specific database
 */
	public mysqlConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {
		}

		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/obldatabase","root","Braude");
			System.out.println("SQL Connected Successfuly");
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
/**
 * log in a specific user	
 * @param userID
 * @param password
 * @return answer "Success" or "Fail" to the running client
 * @throws IOException
 */
	public ArrayList<Object> LogIn(int userID, String password) throws IOException {
		Statement stmt;
		User user;
		ArrayList<Object> res = new ArrayList<>();
        boolean status=false;
		res.add("Login");
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE userID='" + userID + "' AND password='" + password + "';");
			if (rs.next()) {
                status = (rs.getInt("status") == 1);
                if (status)
                {
                        res.add("AlreadyLoggedIn");
                }
                else
                {
                   res.add("OK");
                   user= new User(rs.getInt("userID"),rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("phone"), rs.getString("email"),
                   rs.getString("role"), rs.getString("status"), rs.getString("cardStatus"));
                  res.add((Object)user);
                }
                
                res.add(rs.getInt("id"));
                res.add(rs.getString("role"));
        }
		else res.add("Fail");
        rs.close();
        
        Statement stmtUpdt;
        stmtUpdt = conn.createStatement();
        int result = stmtUpdt
                        .executeUpdate("UPDATE users SET status=1 WHERE userID='"
                                        + userID + "' AND password='" + password + "';");
        
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}
/**
 * 	log out a specific user
 * @param id
 * @return answer "Success" or "Fail" to the running client
 */
	public ArrayList<Object> LogOut(int id) {
        Statement stmt;
        ArrayList<Object> res = new ArrayList<>();
        res.add("LogOut");
        try {
                Statement stmtUpdt;
                stmtUpdt = conn.createStatement();
                int result = stmtUpdt
                                .executeUpdate("UPDATE users SET status=0 WHERE userID='"+ id + "';");
                if (result == 1) {
                        res.add("Success");
                } else {
                        res.add("Error");
                }
        } catch (SQLException e) {
                e.printStackTrace();
        }
        res.add("Error");
        return res;
	}
/**
 * search for a specific book by his name in the database	
 * @param bname book name
 * @return arraylist that contains the search results
 */
	 public ArrayList<Object> searchBookName(String bname) {

         ArrayList<Object> res = new ArrayList<Object>();
         res.add("BookSearch");
         Statement stmt;
         try {
                 stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT * FROM books WHERE name='" + bname + "';");

                 if (rs.next()) {
                         res.add("OK");
                         rs.previous();

                         while (rs.next()) {
                        	 String[] book = new String[4];
                        	 String bCode = Integer.toString(rs.getInt("bookCode"));
                                 book[0]=bCode;
                                 book[1]=rs.getString("name");
                                 book[2]=rs.getString("authors");
                                 book[3]=rs.getString("shelf");
                                 res.add(book);
                                 }

                 } else
                         res.add("Fail");

                 rs.close();
         } catch (SQLException e) {
                 e.printStackTrace();
                 res.add("Error");
         }
         return res;
	 }
	 /**
	  * search for a specific book by his author name in the database	
	  * @param aname author name
	  * @return arraylist that contains the search results
	  */	 
	 public ArrayList<Object> searchBookAuthor(String aname) {

         ArrayList<Object> res = new ArrayList<Object>();
         res.add("BookSearch");
         Statement stmt;
         try {
                 stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT * FROM books WHERE authors='" + aname + "';");

                 if (rs.next()) {
                         res.add("OK");
                         rs.previous();

                         while (rs.next()) {
                        	 String[] book = new String[4];
                        	 String bCode = Integer.toString(rs.getInt("bookCode"));
                                 book[0]=bCode;
                                 book[1]=rs.getString("name");
                                 book[2]=rs.getString("authors");
                                 book[3]=rs.getString("shelf");
                                 res.add(book);
                                 }

                 } else
                         res.add("Fail");

                 rs.close();
         } catch (SQLException e) {
                 e.printStackTrace();
                 res.add("Error");
         }
         return res;
	 }
	 /**
	  * search for a specific book by his subject in the database	
	  * @param subject subject name
	  * @return arraylist that contains the search results
	  */	 	 
	 public ArrayList<Object> searchBookSubject(String subject) {

         ArrayList<Object> res = new ArrayList<Object>();
         res.add("BookSearch");
         Statement stmt;
         try {
                 stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT * FROM books,subject,subjectsofbooks WHERE books.bookCode=subjectsofbooks.bookOfSubjectCode "
                         + "AND subject.subID=subjectsofbooks.subjectID AND subject.subject='"
                         + subject + "';");

                 if (rs.next()) {
                         res.add("OK");
                         rs.previous();

                         while (rs.next()) {
                        	 String[] book = new String[4];
                        	 String bCode = Integer.toString(rs.getInt("bookCode"));
                                 book[0]=bCode;
                                 book[1]=rs.getString("name");
                                 book[2]=rs.getString("authors");
                                 book[3]=rs.getString("shelf");
                                 res.add(book);
                                 }

                 } else
                         res.add("Fail");

                 rs.close();
         } catch (SQLException e) {
                 e.printStackTrace();
                 res.add("Error");
         }
         return res;
	 }
	 /**
	  * search for copies of a specific bookCode	
	  * @param bookID bookCode
	  * @return arraylist that contains the search results
	  */	 
	 public ArrayList<Object> searchCopyID(int bookID) {

         ArrayList<Object> res = new ArrayList<Object>();
         res.add("SearchCopyID");
		 Statement stmt = null;
			
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {

				e.printStackTrace();
			}
			
			ResultSet rs;
			try {
				rs = stmt.executeQuery("SELECT tmp.copyOrderID, tmp.count FROM bookcopies, (SELECT copyOrderID, COUNT(*) AS count FROM orders WHERE orderStatus='active' GROUP BY copyOrderID HAVING count>=0) AS tmp WHERE bookcopies.copyID=tmp.copyOrderID AND bookcopies.bookCode = '"+bookID+"';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				
				while (rs.next()) {
					String[] copy = new String[2];
					String copyID = Integer.toString(rs.getInt(1));
					String count = Integer.toString(rs.getInt(2));
					copy[0]=copyID;
					copy[1]=count;
					System.out.println(copy[0]);
					System.out.println(copy[1]);
					res.add(copy);
				}
			}
				ResultSet rs1=stmt.executeQuery("SELECT bookcopies.copyID FROM bookcopies WHERE bookcopies.bookCode='"+bookID+"' AND bookcopies.copyID NOT IN (SELECT tmp.copyOrderID AS copyID FROM bookcopies, (SELECT copyOrderID FROM orders WHERE orderStatus='active' GROUP BY copyOrderID) AS tmp WHERE bookcopies.copyID=tmp.copyOrderID AND bookcopies.bookCode = '"+bookID+"');");
				if(rs1.next())
				{
					rs1.previous();
					if (res.size()==1) res.add("OK");
					while (rs1.next()) {
						String[] copy = new String[2];
						String copyID = Integer.toString(rs1.getInt(1));
						copy[0]=copyID;
						copy[1]="0";
						res.add(copy);
					}
				}

			 else res.add("Fail");
				
			rs.close();
			} catch (SQLException e) {
				res.add("Error");
			}
			return res;
	 	}
	 
	 /**
	  * search for a specific book by description in the database	
	  * @param desc description
	  * @return arraylist that contains the search results
	  */	 
	 public ArrayList<Object> searchBookDesc(String desc) {

         ArrayList<Object> res = new ArrayList<Object>();
         String[] spltBookDescription;
         String[] spltUserDescription = desc.split(" ");
         res.add("BookSearch");
         Statement stmt;
         try {
                 stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT * FROM books;");

                 if (rs.next()) {
                         res.add("OK");
                         rs.previous();
                         
                         
                         while (rs.next()) {
                        	 
                        	 spltBookDescription = rs.getString("summary").split(" ");

                     if (desCompare(spltBookDescription, spltUserDescription)) {

                        	 String[] book = new String[4];
                        	 String bCode = Integer.toString(rs.getInt("bookCode"));
                                 book[0]=bCode;
                                 book[1]=rs.getString("name");
                                 book[2]=rs.getString("authors");
                                 book[3]=rs.getString("shelf");
                                 res.add(book);
                                 }
                         }

                 } else
                         res.add("Fail");

                 rs.close();
         } catch (SQLException e) {
                 e.printStackTrace();
                 res.add("Error");
         }
         return res;
	 }

	 /**
	  * compare between free description and the summary of a specific book in the database	
	  * @param spltBookDescription
	  * @param spltUserDescription
	  * @return equals or not
	  */
	 public boolean desCompare(String[] spltBookDescription, String[] spltUserDescription) {
     for (int i = 0; i < spltBookDescription.length; i++) {
             for (int j = 0; j < spltUserDescription.length; j++) {

                     if (spltBookDescription[i].equals(spltUserDescription[j])) {
                             return true;
                     }

             }
     }
     return false;

	 }
	/**
	 * check for users who are late in returns 
	 * @return arraylist that contains the users who late
	 */
	 public ArrayList<Object> checkReturns() {

         ArrayList<Object> res = new ArrayList<Object>();
         Date date = new Date();
         Date tommorowDate = new Date(date.getTime() + (1000 * 60 * 60 * 24));

         DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

         String tDate = dateFormat.format(tommorowDate).toString();
         Statement stmt;
         try {
                 stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT borrows.copyID ,users.email, users.username FROM borrows,users WHERE users.userID=borrows.userID AND borrows.returnDate ='" + tDate + "';");

                 while (rs.next()) 
                 {
                	 
                	 String[] users = new String[3];
                	 String copy=Integer.toString(rs.getInt("copyID"));
                	 users[0]=copy;
                	 users[1]=rs.getString("email");
                	 users[2]=rs.getString("username");
                	 System.out.println(users[0]+ " " +users[1]+ " " + tDate);
                     res.add(users);
                 }

                 rs.close();
         } catch (SQLException e) {
                 e.printStackTrace();
                 res.add("Error");
         }

         return res;

	 }

	 /**
	  * freezes the  users who should be frozen
	  * @throws SQLException
	  */
	 public void checkFreeze() throws SQLException
	 {
		 Statement stmt = null;
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			System.out.println(today);
			ResultSet rs1 = null;
			ResultSet rs = stmt.executeQuery("SELECT * FROM borrows, users WHERE borrows.borrowStatus='active' AND borrows.userID=users.userID AND users.cardStatus='ok';");
			if(rs.next())
			{
				rs.previous();
				while(rs.next())
				{
					String returnDate=rs.getString("returnDate");
					int borrowID=rs.getInt("borrowID");
					Date d1 = null;
					Date d2 = null;
					try {
						d1 = (Date)sdf.parse(returnDate);
						d2 = (Date)sdf.parse(today);
					} catch (ParseException e) {
						e.printStackTrace();
					}

					if(d1.getTime()<d2.getTime())
					{

						Statement stmtUpdt = conn.createStatement();
						System.out.println(borrowID);
						Statement st = null;
						st = conn.createStatement();
						rs1=st.executeQuery("SELECT borrowErrorID FROM errors WHERE borrowErrorID='"+borrowID+ "'AND errorStatus='active';");
						int flag=0;
						int result;
						if(rs1.next())
						{
							int borrowErrorID;
							rs1.previous();
							while(rs1.next())
							{
								borrowErrorID=rs1.getInt("borrowErrorID");
								if(borrowErrorID==borrowID)
								{
									flag=1;
									break;
								}
							}
							if(flag==0)
							{
								result = stmtUpdt.executeUpdate("INSERT INTO errors (errorStatus, errorDate, borrowErrorID) VALUES('active','"+ today+ "','"+ borrowID +"');");
							}
						}
						else
							result = stmtUpdt.executeUpdate("INSERT INTO errors (errorStatus, errorDate, borrowErrorID) VALUES('active','"+ today+ "','"+ borrowID +"');");
					}
				}
			}
			rs.close();
			
			rs = stmt.executeQuery("SELECT userID,copyID,returnDate FROM borrows WHERE borrowStatus='active';");
			if(rs.next())
			{
				rs.previous();
				while(rs.next())
				{
					String returnDate=rs.getString("returnDate");
					Date d1 = null;
					Date d2 = null;
					try {
						d1 = (Date)sdf.parse(returnDate);
						d2 = (Date)sdf.parse(today);
					} catch (ParseException e) {
						e.printStackTrace();
					}

					if(d1.getTime()<d2.getTime())
					{
						String userID = Integer.toString(rs.getInt("userID"));
						Statement stmtUpdt = conn.createStatement();
						int result = stmtUpdt.executeUpdate("UPDATE users SET cardStatus='frozen' WHERE userID='"+userID+"';");
					}
				}
			}
			rs.close();
	 }
	 
}